package com.tk.exam.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author zhouyuebin
 * @Description:
 * @date Create in 10:03 2018/12/11
 */
public class ExcelUtils {


    public static List<List<String>> read(InputStream inputStream, boolean isExcel2003)throws IOException {

        List<List<String>> dataLst = null;
        /** 根据版本选择创建Workbook的方式 */
        Workbook wb = null;
        if (isExcel2003){
            wb = new HSSFWorkbook(inputStream);
        }else{
            wb = new XSSFWorkbook(inputStream);
        }
        dataLst = readDate(wb);
        return dataLst;
    }


    /**
     *
     * 读取数据
     *
     * @param wb
     * @return
     * @see [类、类#方法、类#成员]
     */
    private static List<List<String>> readDate(Workbook wb){

        List<List<String>> dataLst = new ArrayList<List<String>>();
        /** 得到第一个shell */
        Sheet sheet = wb.getSheetAt(0);
        /** 得到Excel的行数 */
        int totalRows = sheet.getPhysicalNumberOfRows();

        int totalCells = 0;
        /** 得到Excel的列数 */
        if (totalRows >= 1 && sheet.getRow(0) != null){
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }

        /** 循环Excel的行 */
        for (int r = 0; r < totalRows; r++){
            Row row = sheet.getRow(r);
            if (isEmptyRow(row)){
                continue;
            }

            List<String> rowLst = new ArrayList<String>();

            /** 循环Excel的列 */
            for (int c = 0; c < totalCells; c++){
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (null != cell){
                    // 以下是判断数据的类型
                    switch (cell.getCellType()){
                        case Cell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                //如果是date类型则 ，获取该cell的date值
                                Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                cellValue = format.format(date);;
                            }else {// 纯数字
                                BigDecimal big=new BigDecimal(cell.getNumericCellValue());
                                cellValue = big.toString();
                                //解决1234.0  去掉后面的.0
                                if(null!=cellValue&&!"".equals(cellValue.trim())){
                                    String[] item = cellValue.split("[.]");
                                    if(1<item.length&&"0".equals(item[1])){
                                        cellValue=item[0];
                                    }
                                }
                            }
                            break;


                        case HSSFCell.CELL_TYPE_STRING: // 字符串
                            cellValue = cell.getStringCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                            cellValue = cell.getBooleanCellValue() + "";
                            break;

                        case HSSFCell.CELL_TYPE_FORMULA: // 公式
                            cellValue = cell.getCellFormula() + "";
                            break;

                        case HSSFCell.CELL_TYPE_BLANK: // 空值
                            cellValue = "";
                            break;

                        case HSSFCell.CELL_TYPE_ERROR: // 故障
                            cellValue = "非法字符";
                            break;

                        default:
                            cellValue = "未知类型";
                            break;
                    }
                }
                rowLst.add(cellValue);
            }
            /** 保存第r行的第c列 */
            dataLst.add(rowLst);
        }
        return dataLst;
    }

    private static Boolean isEmptyRow (Row row) {
        if(row == null || row.toString().isEmpty()) {
            return true;
        } else {
            Iterator<Cell> it = row.iterator();
            boolean emptyFlag = true;
            while(it.hasNext()) {
                Cell cell = it.next();
                if(cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                    emptyFlag = true;
                } else {
                    emptyFlag = false;
                    break;
                }
                return emptyFlag;
            }
        }
        return false;
    }

    public static HSSFWorkbook exportExcel(String[] header, String sheetName, List<Map<String,Object>> list, String[] headerKey){
        HSSFWorkbook book = new HSSFWorkbook();
        HSSFSheet sheet = book.createSheet(sheetName);
        //设置表头
        HSSFRow row = sheet.createRow(0);
        row = sheet.createRow(0);
        for(int i=0;i<header.length;i++){
            row.createCell(i).setCellValue(header[i]);
        }
        int index = 0;//记录额外创建的sheet数量
        for (int i = 0; i < list.size(); i++) {
            if ((i + 1) % 65535 == 0) {
                sheet = book.createSheet(sheetName + index);
                row = sheet.createRow(0);
                for(int j=0;j<header.length;j++){
                    row.createCell(j).setCellValue(header[j]);
                }
                index++;
                row = sheet.createRow((i + 2) - (index * 65535));
            }else {
                row = sheet.createRow((i + 1) - (index * 65535));
            }
            // 第四步，创建单元格，并设置值
            for (int k=0;k<headerKey.length;k++){
                row.createCell((short) k).setCellValue((String) list.get(i).get(headerKey[k]));

            }
        }
        return book;
    }
}
